﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using SharpDB.Entity;
using System.Timers;
using System.Text.RegularExpressions;
using System.Collections.Concurrent;
using System.Collections;
using System.IO;


namespace SharpDB.DDTekOracle
{
    public class DBInfo
    {
        /// <summary>
        /// 当前数据连接
        /// </summary>
        private OracleDB Db
        {
            get; set;
        }

        internal Timer timer = null;

        internal static IntervalMgr intervalMgr = null;

        private List<string> _TableNames = new List<string>();


        /// <summary>
        /// 缓存对象
        /// </summary>
        private static Dictionary<string, object> Cache = null;


        /// <summary>
        /// 内部缓存的过期依赖于表结构更改时间
        /// </summary>
        private Dictionary<string, DateTime> _Dict_TableStruct_Modify = new Dictionary<string, DateTime>();
        /// <summary>
        /// 表以及最后更新时间(只针对Oracle/Oracle/MySql。MySql的所有表存储引擎必须为MyISAM。)
        /// </summary>
        public Dictionary<string, DateTime> Dict_TableStruct_Modify
        {
            get { return _Dict_TableStruct_Modify; }
        }



        public DBInfo(OracleDB db)
        {
            this.Db = db;
            Cache = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
            Reflesh();
            //开启轮询 查询数据库的表结构是否改变
            timer = new Timer();
            timer.Interval = 3 * 1000;
            timer.Elapsed += Timer_Elapsed;
            intervalMgr = new IntervalMgr(timer, timer.Interval, 3);
            timer.Start();
        }



        /// <summary>
        /// 间隔时间段内查询 表结构是否有改变
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Timer_Elapsed(object sender, ElapsedEventArgs e)
        {
            var isChange = Reflesh();

            if (intervalMgr != null)
            {
                intervalMgr.InsertChangePoint(isChange);
            }
        }

        /// <summary>
        /// 即时刷新 获取最新表结构数据,返回是否 表结构是否有更新
        /// </summary>
        public bool Reflesh()
        {
            Dictionary<string, DateTime> dict_New = new Dictionary<string, DateTime>();

            var resKV = GetTableStruct_Modify();
            dict_New = resKV.Key;

            this._TableNames = resKV.Value;
            this._Dict_TableStruct_Modify = dict_New;

            var arrExcept = dict_New.Except(Dict_TableStruct_Modify);
            var isChange = arrExcept.Any();

            if (isChange)
            {
                Cache["TableNames"] = _TableNames;

                foreach (var item in arrExcept)
                {
                    string tableName = item.Key;
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    List<string> lstColName = lstColInfo.Select(t => t.ColumnName).ToList();
                    Cache[tableName] = lstColName;
                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + ":" + tableName] = colInfo;
                    }
                }
            }
            return isChange;
        }

        /// <summary>
        /// 重置  查询表结构更改的 定时器 的 Interval 间隔
        /// </summary>
        public void ResetInterval()
        {
            if (intervalMgr != null)
            {
                intervalMgr.ReStart();
            }
        }

        /// <summary>
        /// 定时器 查询表结构的 间隔（单位：毫秒）
        /// </summary>
        public double TimerInterval
        {
            get
            {
                if (timer != null)
                    return timer.Interval;
                else
                    return 0;
            }
        }

        /// <summary>
        /// 返回：数据库所有表及对应的表结构修改历史 与 所有表名
        /// </summary>
        /// <returns></returns>
        private KeyValuePair<Dictionary<string, DateTime>, List<string>> GetTableStruct_Modify()
        {

            Dictionary<string, DateTime> dict_SD = new Dictionary<string, DateTime>();
            List<string> lstTabNames = new List<string>();

            DataTable data = new DataTable("table");
            string selSql = string.Empty;
            selSql = string.Format("select object_name,last_ddl_time from user_objects Where object_Type='TABLE' {0} Order By last_ddl_time Desc", Utils.Util.SqlIn("object_name", Db.CustomOperaTingTables.ToArray()));
            data = Db.QryTable(selSql);
            foreach (DataRow dr in data.Rows)
            {
                dict_SD.Add(dr["object_name"].ToString(), Convert.ToDateTime(dr["last_ddl_time"]));
                lstTabNames.Add(dr["object_name"].ToString());
            }

            dict_SD = dict_SD.Where(t => Regex.IsMatch(t.Key, @"^[A-Z_$\d]+$")).ToDictionary(k => k.Key, v => v.Value);
            //过滤 Oracle 中表名有小写的情况
            lstTabNames = lstTabNames.Where(t => Regex.IsMatch(t, @"^[A-Z_$\d]+$")).ToList();

            return new KeyValuePair<Dictionary<string, DateTime>, List<string>>(dict_SD, lstTabNames);
        }





        /// <summary>
        /// 获取当前数据库的所有表名
        /// </summary>
        public List<string> TableNames
        {
            get
            {
                if (!Cache.ContainsKey("TableNames"))
                {
                    Cache["TableNames"] = _TableNames;
                    return _TableNames;
                }
                return (Cache["TableNames"] as List<string>) ?? new List<string>();
            }
        }

        /// <summary>
        /// 根据表名获取所有列名
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <returns>所有列名</returns>
        public List<string> this[string tableName]
        {
            get
            {
                if (!Cache.ContainsKey(tableName))
                {
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    List<string> lstColName = lstColInfo.Select(t => t.ColumnName).ToList();

                    Cache[tableName] = lstColName;

                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + ":" + tableName] = colInfo;
                    }
                }
                return Cache[tableName] as List<string>;
            }
        }

        /// <summary>
        /// 根据表名、列名获取列信息
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public ColumnInfo this[string tableName, string columnName]
        {
            get
            {
                string key = columnName + ":" + tableName;
                if (!Cache.ContainsKey(key))
                {
                    List<ColumnInfo> lstColInfo = GetAllColumnInfo(tableName);
                    foreach (ColumnInfo colInfo in lstColInfo)
                    {
                        Cache[colInfo.ColumnName + ":" + tableName] = colInfo;
                    }
                }
                return Cache[key] as ColumnInfo;
            }
        }

        /// <summary>
        /// 当前数据库是否存在当前表
        /// </summary>
        /// <param name="table_Name">表名</param>
        /// <returns></returns>
        public bool IsExistTable(string table_Name)
        {
            return TableNames.Contains(table_Name, StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// 当前表是否存在当前列
        /// </summary>
        /// <param name="table_Name">表名</param>
        /// <param name="column_Name">列名</param>
        /// <returns></returns>
        public bool IsExistColumn(string table_Name, string column_Name)
        {
            return this[table_Name].Contains(column_Name, StringComparer.OrdinalIgnoreCase);
        }

        /// <summary>
        /// 当前数据库所有的表名及对应所有的列信息
        /// </summary>
        public Dictionary<string, List<ColumnInfo>> GetAllTableColumnInfo()
        {
            Dictionary<string, List<ColumnInfo>> dict = new Dictionary<string, List<ColumnInfo>>(StringComparer.OrdinalIgnoreCase);
            foreach (string tabName in TableNames)
            {
                List<ColumnInfo> lstCol = GetAllColumnInfo(tabName);
                dict.Add(tabName, lstCol);
            }
            return dict;
        }

        /// <summary>
        /// 增加或更新 表 注释说明
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="comment">表说明</param>
        /// <returns></returns>
        public bool UpsertTableComment(string tableName, string comment)
        {
            tableName = tableName.ToUpper();
            comment = comment.Replace("'", "");
            string upsert_sql = string.Empty;
            bool result = false;
            upsert_sql = @"comment on table {0} is '{1}'";
            upsert_sql = string.Format(upsert_sql, tableName, comment);
            result = Db.ExecSql(upsert_sql) > 0;
            return result;
        }

        /// <summary>
        ///  增加或更新 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <param name="comment">列说明</param>
        /// <returns></returns>
        public bool UpsertColumnComment(string tableName, string columnName, string comment)
        {
            tableName = tableName.ToUpper();
            columnName = columnName.ToUpper();
            comment = comment.Replace("'", "");
            string upsert_sql = string.Empty;
            bool result = false;
            upsert_sql = @"comment on column {0}.{1} is '{2}'";
            upsert_sql = string.Format(upsert_sql, tableName, columnName, comment);
            result = Db.ExecSql(upsert_sql) > 0;
            return result;
        }

        /// <summary>
        /// 获取 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public string GetColumnComment(string tableName, string columnName)
        {
            tableName = tableName.ToUpper();
            columnName = columnName.ToUpper();
            string result = string.Empty;
            string strSql = string.Empty;
            strSql = string.Format(@"Select  comments From user_col_comments Where table_name ='{0}' And column_name='{1}'", tableName, columnName);
            result = Db.QrySingle<string>(strSql);
            return result;
        }

        /// <summary>
        /// 获取 列 注释说明
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public Dictionary<string, string> GetColumnsComment(string tableName)
        {
            tableName = tableName.ToUpper();
            Dictionary<string, string> dict_result = new Dictionary<string, string>();
            string strSql = string.Empty;
            strSql = string.Format(@"Select  column_name ,comments From user_col_comments Where table_name ='{0}'", tableName);
            dict_result = Db.QryTable(strSql).GetDict<string, string>("column_name", "comments");
            return dict_result;
        }

        /// <summary>
        /// 获取数据库所有表列注释
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, string> GetAllTableComment()
        {
            string strSql = string.Empty;
            Dictionary<string, string> dict = new Dictionary<string, string>();
            strSql = @"Select table_Name ,Comments From User_Tab_Comments Where table_Type='TABLE' " + Utils.Util.SqlIn("table_Name", Db.CustomOperaTingTables.ToArray()) + " Order By table_Name Asc";
            dict = Db.QryTable(strSql).GetDict<string, string>("table_Name", "Comments");
            return dict;
        }

        /// <summary>
        /// 获取数据库当前表的注释
        /// </summary>
        /// <returns></returns>
        public string GetTableComment(string tableName)
        {
            tableName = tableName.ToUpper();
            string result = string.Empty;
            string strSql = string.Empty;
            strSql = @"Select Comments As Value From User_Tab_Comments  Where table_Type='TABLE' and table_Name='" + tableName + "'";
            result = Db.QrySingle<string>(strSql);
            return result;
        }

        /// <summary>
        /// 获取当前表的所有列信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public List<ColumnInfo> GetAllColumnInfo(string tableName)
        {
            tableName = tableName.ToUpper();
            string strSql = string.Empty;
            List<ColumnInfo> lstCols = new List<ColumnInfo>();
            strSql = string.Format(@"select a.COLUMN_ID As Colorder,a.COLUMN_NAME As ColumnName,a.DATA_TYPE As TypeName,b.comments As DeText,(Case When a.DATA_TYPE='NUMBER' Then a.DATA_PRECISION Else a.DATA_LENGTH End )As Length,a.DATA_SCALE As Scale,
(Case When (select Count(1)  from user_cons_columns aa, user_constraints bb 
 where aa.constraint_name = bb.constraint_name 
 and bb.constraint_type = 'P' and aa.table_name = '{0}' And aa.column_name=a.COLUMN_NAME)>0 Then 1 Else Null End
 ) As IsPK,
  Case a.NULLABLE  When 'Y' Then 1 Else Null End As CanNull,
  a.data_default As DefaultVal 
from user_tab_columns a 
Inner Join user_col_comments b On a.TABLE_NAME=b.table_name 
Where b.COLUMN_NAME= a.COLUMN_NAME   and a.Table_Name='{0}'  order by a.column_ID Asc", tableName);
            lstCols = Db.QryTable(strSql).ConvertToListObject<ColumnInfo>();
            return lstCols;
        }


        /// <summary>
        /// 得到当前数据库所有视图名称
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllViews()
        {
            List<string> lstView = new List<string>();
            string strSql = "select view_name as Name from user_views Order By view_name Asc";
            lstView = Db.QryTable(strSql).GetFirstCol<string>();
            return lstView;
        }






        /// <summary>
        /// 获取所有表、表描述、包含列的相关信息
        /// </summary>
        /// <returns></returns>
        public List<TableInfo> GetAllTableInfos()
        {
            List<TableInfo> lstTabInfo = new List<TableInfo>();
            Dictionary<string, string> dict_tabComment = GetAllTableComment();
            Dictionary<string, List<ColumnInfo>> dictColInfo = GetAllTableColumnInfo();

            foreach (var item in dict_tabComment)
            {
                TableInfo tabInfo = new TableInfo();
                tabInfo.TableName = item.Key;
                tabInfo.TabComment = item.Value;
                if (dictColInfo.ContainsKey(tabInfo.TableName))
                {
                    List<ColumnInfo> lstColInfo = dictColInfo[tabInfo.TableName];
                    tabInfo.LstColInfo = lstColInfo;
                    lstTabInfo.Add(tabInfo);
                }
                else
                {

                }
            }
            return lstTabInfo;
        }



        public bool DropTable(string tableName)
        {
            bool result = false;
            string drop_sql = string.Empty;
            drop_sql = "drop table " + tableName;
            Db.ExecSql(drop_sql);
            result = true;
            return result;
        }

        public bool DropColumn(string tableName, string columnName)
        {
            bool result = false;
            string drop_sql = string.Empty;
            drop_sql = "alter table {0} drop column {1}";
            drop_sql = string.Format(drop_sql, tableName, columnName);
            Db.ExecSql(drop_sql);
            result = true;
            return result;
        }
    }
}

